---
title: "pk82289_legalnet_letters stored procedure documentation"
summary: "Analysis of Oracle package Pk82289_LegalNet_Letters (WCIS_DBA), which retrieves and updates LegalNet letter-request data for Xpressions letter printing."
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk82289_legalnet_letters"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --owner SAIF --name pk82289_legalnet_letters"
retrieval_date: "2026-08-31"
---

# pk82289_legalnet_letters

> [!NOTE]
> The Legends CLI query for this package returned only the package **specification**; no package **body** was available for this run (this is normal/expected for this API — `DBA_SOURCE`/`ALL_SOURCE` exposes only what is granted). Procedure descriptions and business-rule inferences below are therefore derived from the calling VB.NET code in `clsLetterRequest.vb` and `clsLetterRequestCollection.vb` rather than from actual PL/SQL body logic, and are marked with `*`.

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | WCIS_DBA (Oracle) |
| **Calling libraries** | `SAIF.Business.Claim.Legal` |
| **Calling classes** | `clsLetterRequest` |
| **Member count** | 4 |

## Purpose

`Pk82289_LegalNet_Letters` supports the LegalNet letter-request workflow used to generate Xpressions print output for legal-case correspondence.* Based on the calling code in `clsLetterRequest.vb` and `clsLetterRequestCollection.vb`, the package retrieves worker/claim identification data for a legal case (`GetWorkerInfo`), lists outstanding letter requests (`SelectLetterRequests`), retrieves a single letter request's detail by its sequence number (`SelectLetter`), and records the printed date once a letter has been generated (`UpdateLetterRequest`).*

## Procedures / Methods

### GetWorkerInfo

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR (`recordset`) of worker/claim identification data |
| **Called by** | `clsLetterRequest.GetWorkerInfo` (`Print\clsLetterRequest.vb`, property at line 1211; private method around line 926) |

**Description:** Called from `clsLetterRequest.GetWorkerInfo`, which passes `MyCriteria.LegalCaseSeqNum` as `in_legalCaseSeq` and reads the returned cursor for `SaifClaimNum`, `PolicyNum`, `PrimaryTaxId`, `WorkerFirstName`, `WorkerLastName`, `injurydate`, and `RequestDate`. Returns worker and claim identification information for the given legal case, used to populate letter merge fields for printing.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_legalCaseSeq` | IN | `LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE` | — | Legal case sequence number identifying the case whose worker/claim info is being retrieved. |
| `recordset` | IN OUT | `ref_cursor` | — | Output cursor returning worker/claim identification rows. |

### SelectLetterRequests

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR (`recordset`) of letter-request rows |
| **Called by** | `clsLetterRequestCollection` (`Print\clsLetterRequestCollection.vb:324`, via `RetrieveSQL` used in `BeforeRetrieve`) |

**Description:** Bound as `RetrieveSQL` for `clsLetterRequestCollection`, whose `BeforeRetrieve` override sends this statement with no additional filter parameters visible at the call site. Returns the set of outstanding/queued letter requests used to populate a `clsLetterRequestCollection` for batch letter processing.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | IN OUT | `ref_cursor` | — | Output cursor returning letter-request rows. |

### SelectLetter

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR (`recordset`) of a single letter-request's detail |
| **Called by** | `clsLetterRequest.RetrieveSQL` (`Print\clsLetterRequest.vb:1197`); also `clsLetterRequestCollection.RetrieveByRequestSeq` |

**Description:** Bound as `RetrieveSQL` for `clsLetterRequest`; `BeforeRetrieve` passes `MyCriteria.LetterRequestSeqNum` as `inLetterRequestSeqNum` and the resulting reader is used to `Fill` the object's fields (`LegalCaseSeqNum`, `LetterRequestSeqNum`, `LetterTypeCode`, `Claimant`, `NumCopies`, `DefaultPrinter`, `PrintedDate`). Retrieves the full detail of a single letter request by its sequence number, for display/printing of one letter.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inLetterRequestSeqNum` | IN | `NUMBER` | — | Sequence number of the letter request to retrieve. |
| `recordset` | IN OUT | `ref_cursor` | — | Output cursor returning the single letter-request row. |

### UpdateLetterRequest

| Field | Value |
| --- | --- |
| **Returns** | Row count (rows affected), consumed via `NonQuery` request in the caller |
| **Called by** | `clsLetterRequest.UpdateSQL` (`Print\clsLetterRequest.vb:1203`), invoked from `BeforeUpdate` |

**Description:** Bound as `UpdateSQL` for `clsLetterRequest`; `BeforeUpdate` sends `_printedDate` as `inPrintedDate` and `MyCriteria.LetterRequestSeqNum` as `inLetterRequestSeqNum`, and `AfterUpdate` verifies exactly one row was affected. Marks a letter request as printed by recording its printed date, keyed by the letter-request sequence number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inPrintedDate` | IN | `LETTER_REQUEST.PRINTED_DATE%TYPE` | — | Date the letter was printed, to be stored against the letter request. |
| `inLetterRequestSeqNum` | IN | `LETTER_REQUEST.LETTER_REQUEST_SEQ_NUM%TYPE` | — | Sequence number of the letter request being updated. |

## Business Rules

Rules extracted from the calling VB.NET code (inferred; no package body was available for direct confirmation):

## BR-PKG3-001: Letter request update expects a single affected row

| Field | Value |
| --- | --- |
| **Statement** | After calling `UpdateLetterRequest`, `clsLetterRequest.AfterUpdate` checks that exactly one row was affected by the update.* |
| **Trigger** | Saving a `clsLetterRequest` object (letter marked as printed). |
| **Enforcement** | `clsLetterRequest.AfterUpdate` in `Print\clsLetterRequest.vb`.* |
| **Outcome** | A rows-affected count other than 1 indicates the letter-request sequence number did not match exactly one row; calling code handling implies an error/exception path.* |
| **Source** | `Print\clsLetterRequest.vb` |

---

## BR-PKG3-002: Printed date defaults by letter type before update

| Field | Value |
| --- | --- |
| **Statement** | For `RDISC` letters, `_printedDate` is set to the file-create date; for `DEFLT` letters, `_printedDate` is set to today's date, before `UpdateLetterRequest` is called to persist the printed date.* |
| **Trigger** | `PostFill()` runs after a `clsLetterRequest` is retrieved (`SelectLetter`), based on the letter's `LetterTypeCode`. |
| **Enforcement** | `clsLetterRequest.PostFill` in `Print\clsLetterRequest.vb`.* |
| **Outcome** | The value ultimately written via `inPrintedDate` depends on letter type rather than always reflecting the actual print time.* |
| **Source** | `Print\clsLetterRequest.vb` |

---

## BR-PKG3-003: Worker information is scoped to a single legal case

| Field | Value |
| --- | --- |
| **Statement** | `GetWorkerInfo` is always invoked with a single `in_legalCaseSeq` value (`MyCriteria.LegalCaseSeqNum`) and is expected to return worker/claim data for that one legal case.* |
| **Trigger** | `clsLetterRequest.PostFill` calls `GetWorkerInfo()` after the letter request is filled. |
| **Enforcement** | `clsLetterRequest.GetWorkerInfo` (private) in `Print\clsLetterRequest.vb`.* |
| **Outcome** | Merge fields such as claimant name, claim number, and injury date are populated per-legal-case for the letter being printed.* |
| **Source** | `Print\clsLetterRequest.vb` |

> These rules are documented in [business-rules.md](../business-rules.md). Newly introduced rules must be added to `business-rules.md` as part of the same writing task.

## Operations

Operations and processes implemented via this package:

| Operation | Description | Trigger |
| --- | --- | --- |
| Letter request generation | Retrieves letter-request detail and related worker/claim data, then records the printed date once the letter is produced for Xpressions printing.* | A pending letter request is opened for printing (`clsLetterRequest` retrieve/update cycle). |
| Letter request listing | Retrieves the collection of outstanding letter requests for batch processing.* | `clsLetterRequestCollection` is populated for a print run. |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk82289_legalnet_letters
PACKAGE Pk82289_LegalNet_Letters AS

TYPE ref_cursor IS REF CURSOR;

PROCEDURE GetWorkerInfo
(in_legalCaseSeq IN LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
 recordset   IN OUT ref_cursor);

PROCEDURE SelectLetterRequests( recordset   IN OUT ref_cursor);

PROCEDURE SelectLetter(inLetterRequestSeqNum IN NUMBER,
                       recordset IN OUT ref_cursor);

PROCEDURE UpdateLetterRequest(inPrintedDate IN LETTER_REQUEST.PRINTED_DATE%TYPE ,
 		 				 inLetterRequestSeqNum IN LETTER_REQUEST.LETTER_REQUEST_SEQ_NUM%TYPE);

END Pk82289_LegalNet_Letters;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```text
No package body was returned by the Legends CLI query for WCIS_DBA.pk82289_legalnet_letters in this
run. This is normal/expected for this API: DBA_SOURCE/ALL_SOURCE only exposes source text the
querying account has visibility into, and for this package only the SPEC text was available. No
body source is available to display.
```

</details>

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: WCIS_DBA.pk82289_legalnet_letters (spec only; no body returned)
- Legends CLI query executed 2026-08-31: `legends oracle query --owner SAIF --name pk82289_legalnet_letters`
- Calling code: `SAIF.Business.Claim.Legal\Print\clsLetterRequest.vb`
- Calling code: `SAIF.Business.Claim.Legal\Print\clsLetterRequestCollection.vb`